
Create Function [dbo].[SearchKhordehForoshi] (@KCode Varchar(40), @SystemName Varchar(50) ) 
RETURNS 
   @SearchKhordehForoshi TABLE
   (
    K_Code         varchar(15),
    Barcode        Varchar(40),
    Name1          nVarchar(100),
    A_Code         varchar(3),
    R_Unit1_2      Real,
    Hamrah_Flag    Bit,
    Exp_Flag       Bit,
    Serial_flag    Bit,
    Point_Flag     Bit,
    Make_Flag      Bit,
    CtrMojodi      Bit,
    Duty_Flag      Smallint,
    Price_Kharid   money,
    Price_Forosh   money,
    Price_Consumer Money,
    price_Finish   Money,
    Mojodi         Money,
    LatinOrIr      Char(1) ,
	FinalPrice Money 
   )
As 
BEGIN


  Declare @ACode varchar(31)
  Select @ACode = Sender From Local_Option WITH (NOLOCK) Where Computer_Name = @SystemName
  DECLARE @TaxPercent REAL = (SELECT TOP 1 Max_Percent FROM dbo.Over_Under  WHERE Action_Code=7 AND TaxTransactionType = 3 ORDER BY Tarikh)
  Insert Into @SearchKhordehForoshi
  Select TOP 1 Kalaid.K_Code, Barcode, Name1, Anbar.A_Code, R_Unit1_2,Hamrah_Flag, Exp_Flag, Serial_flag, Point_Flag,  
         Make_Flag, 
         Cast(case
           When Anbarid.CtrMojodi = 0 then 0
           When Anbarid.CtrMojodi = 1 then 1
           When Anbarid.CtrMojodi = 2 And Kalaid.CtrMojodi = 1 then 1   
           When Anbarid.CtrMojodi = 2 And Kalaid.CtrMojodi = 0 then 0 
         End as Bit) as CtrMojodi,
         Cast(Duty_Flag as Smallint) Duty_Flag, Price_Kharid, Price_Forosh, Price_Consumer,Price_Finish,Mojodi, IsNull(LatinOrIr,0) LatinOrIr,

		    case 
               when Duty_Flag =1 and isnull(TaxPercent,0)  > 0 then Price_Forosh+((Price_Forosh * TaxPercent)/100)  
               When Duty_flag=1 And isnull(TaxPercent,0) =0  then  Price_Forosh + ((Price_Forosh *  @TaxPercent )/100)  
               else 
               Price_Forosh 
			   end As FinalSalesForosh
			   

         From Anbar WITH (NOLOCK) INNER join KalaId WITH (NOLOCK) ON kalaid.K_Code = Anbar.K_Code
         Inner join  Anbarid WITH (NOLOCK)
         on anbarid.A_code = Anbar.A_Code 
         Where (Kalaid.K_Code = @KCode Or BarCode = @KCode)  
         And Anbar.A_Code in(Select splitdata From dbo.fnSplitString(@ACode,',')) 
         And Anbar.Active_Flag = '0'
         And Mojodi > (
                       Select Case   
                                When Anbarid.CtrMojodi = 0 then -9999999999   
                                When Anbarid.CtrMojodi = 1 then 0   
                                When Anbarid.CtrMojodi = 2 And Kalaid.CtrMojodi = 1 then 0   
                                When Anbarid.CtrMojodi = 2 And Kalaid.CtrMojodi = 0 then -9999999999 
                              end 
                      ) order By Anbar.A_Code Desc
   RETURN                     
End
